﻿using DotNetDAL.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DotNetDAL.Connector
{

    /// <summary>
    /// 事务处理器
    /// </summary>
    public class SqlServerTransactor : ITransactor, IDisposable
    {

        #region 字段

        /// <summary>
        /// 数据库连接
        /// </summary>
        private SqlConnection conn { get; set; }

        private SqlTransaction tran { get; set; }

        #endregion

        #region 属性

        /// <summary>
        /// 数据库连接
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                if (conn == null)
                {
                    throw new Exception("数据库连接对象为 null ");
                }

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                else if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }

                return conn;
            }
        }

        private ConnectorUtils _Utils { get; set; }
        /// <summary>
        /// 工具
        /// </summary>
        public ConnectorUtils Utils
        {
            get
            {
                if (_Utils == null)
                {
                    _Utils = new ConnectorUtils();
                }
                return _Utils;

            }
        }


        private SqlConnection _cacheConnection { get; set; } = null;
        private SqlServerCacheHelper _CacheHelper { get; set; }
        /// <summary>
        /// 缓存助手
        /// </summary>
        private SqlServerCacheHelper CacheHelper
        {
            get
            {
                if (_CacheHelper == null)
                {
                    if (_cacheConnection == null)
                    {
                        _cacheConnection = new SqlConnection(conn.ConnectionString);
                    }
                    _CacheHelper = new SqlServerCacheHelper(_cacheConnection);
                }
                return _CacheHelper;

            }
        }

        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connection">数据库连接</param>
        public SqlServerTransactor(SqlConnection connection, SqlTransaction tran)
        {
            this.conn = connection;
            this.tran = tran;
        }

        #endregion

        #region IDisposable Support

        private bool disposedValue = false; // 要检测冗余调用
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (!disposedValue)
            {

                //释放数据库连接对象_cacheConnection
                if (_cacheConnection != null)
                {
                    if (_cacheConnection.State == ConnectionState.Open)
                    {
                        _cacheConnection.Close();
                    }
                    _cacheConnection.Dispose();
                    _cacheConnection = null;
                }



                disposedValue = true;
            }
        }
        #endregion

        #region 创建数据库命令
        /// <summary>
        /// 创建数据库命令
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">sql参数</param>
        /// <param name="CommandTimeout">超时时间。以秒为单位</param>
        /// <returns></returns>
        public SqlCommand CreateCommand(string sql = null, SqlParameter[] parameters = null, int CommandTimeout = 60)
        {
            if (tran == null)
            {
                throw new Exception("未开启事务");
            }
            SqlCommand _SqlCommand = new SqlCommand();
            if (!string.IsNullOrEmpty(sql))
            {
                _SqlCommand.CommandText = sql;
            }
            _SqlCommand.Connection = Connection;
            _SqlCommand.Transaction = tran;
            _SqlCommand.CommandType = CommandType.Text;
            //以秒为单位
            _SqlCommand.CommandTimeout = CommandTimeout;

            _SqlCommand.Parameters.Clear();
            if (parameters != null && parameters.Length > 0)
            {
                _SqlCommand.Parameters.AddRange(parameters);
            }
            return _SqlCommand;

        }

        #endregion

        #region ExecuteNonQuery

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        private int ExecuteNonQuery(string sql, SqlParameter[] parameters = null)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new Exception("SQL语句不能为空");
            }
            int rNum = -1;
            using (SqlCommand _SqlCommand = CreateCommand(sql, parameters)){
                rNum = _SqlCommand.ExecuteNonQuery();
            }
            return rNum;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="whereParamObj">实体参数对象或 Dictionary<string, object> </param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public int ExecuteNonQuery(string sql, object whereParamObj = null)
        {
            sql = sql == null ? sql : sql.Trim();
            if (string.IsNullOrEmpty(sql))
            {
                throw new Exception(" sql 不能为空");
            };

            //生成 where 条件的参数
            List<SqlParameter> paramList = SqlServerParamHelper.CreateSqlParameters(whereParamObj);

            return ExecuteNonQuery(sql, paramList.ToArray());
        }

        #endregion

        #region 实体操作-添加数据

        /// <summary>
        /// 实体操作-添加数据
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="paramObj">待添加的实体</param>
        public bool Add(string table_name, object paramObj)
        {
            if (!CacheHelper.IsExistTable(table_name))
            {
                throw new Exception(string.Format("【添加数据实体】表“{0}”不存在", table_name));
            };
            Entity.DbStruct.DbStructColumnCache cahceData = CacheHelper.GetDbStructColumns(table_name);
            DbActionParam<SqlParameter> parObj = SqlServerParamHelper.CreateSqlEntityParameters(cahceData, paramObj);

            string sql = string.Format("insert into {0}({1}) values({2})"
                    , table_name
                    , string.Join(",", parObj.column)
                    , "@" + string.Join(",@", parObj.column)
                );
            return ExecuteNonQuery(sql, parObj.paramList.ToArray()) > 0;

        }
        #endregion

        #region 实体操作-更新数据

        /// <summary>
        /// 实体操作-更新数据
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="updateFieldParam">待更新的实体</param>
        /// <param name="whereSQL">where条件SQL</param>
        /// <param name="whereParamObj">where条件SQL参数</param>
        /// <returns></returns>
        public bool Update(string table_name, object updateFieldParam, string whereSQL, object whereParamObj = null)
        {
            whereSQL = whereSQL == null ? whereSQL : whereSQL.Trim();
            if (string.IsNullOrEmpty(whereSQL))
            {
                throw new Exception(" 【更新数据实体】where 条件不能为空");
            };
            if (!CacheHelper.IsExistTable(table_name))
            {
                throw new Exception(string.Format("【更新数据实体】表“{0}”不存在", table_name));
            };

            //参数列表
            List<SqlParameter> paramList = new List<SqlParameter>();

            //生成 where 条件的参数
            paramList.AddRange(SqlServerParamHelper.CreateSqlParameters(whereParamObj));


            Entity.DbStruct.DbStructColumnCache cahceData = CacheHelper.GetDbStructColumns(table_name);
            string updatePrefix = "u";
            // 生成更新的字段和参数
            DbActionParam<SqlParameter> updateParamObj =
                SqlServerParamHelper.CreateSqlEntityParameters(cahceData, updateFieldParam, updatePrefix);

            // 生成更新的参数
            paramList.AddRange(updateParamObj.paramList);

            List<string> ucols = new List<string>();
            foreach (string pcol in updateParamObj.column)
            {
                ucols.Add(string.Format("{0}=@{1}{0}", pcol, updatePrefix));
            }

            if (ucols.Count < 1)
            {
                throw new Exception("获取不到需要更新的实体的表字段");
            }

            string sql = string.Format("update {0} set {1} {2}"
                    , table_name
                    , string.Join(",", ucols)
                    , (whereSQL.ToLower().StartsWith("where") ? " " : " where ") + whereSQL
                );

            return ExecuteNonQuery(sql, paramList.ToArray()) > 0;
        }

        /// <summary>
        /// 实体操作-更新数据
        /// </summary>
        /// <param name="table_name">表名</param>
        /// <param name="updateFieldParam">待更新的实体</param>
        /// <param name="whereParam">where条件参数</param>
        /// <returns></returns>
        public bool Update(string table_name, object updateFieldParam, WhereParameter whereParam)
        {
            return Update(table_name, updateFieldParam, whereParam.SQL, whereParam.Parameter);
        }


        #endregion


    }
}
